﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using Entities;
using System.Data.SqlClient;
using Entities.Enums;
using System.Configuration;
using System.Data;

namespace SQLDao
{
    public class SQL_Interview_dao : IInterviewDAO
    {
        private SqlConnection connection;
        public SQL_Interview_dao()
        {
            var dao = new SQL_Dao();
            this.connection = SQL_Dao.Connection;
        }

        public Interview LoadInterview(int intervID)
        {
            Interview interv = new Interview();

            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.Parameters.Add(new SqlParameter("@id", intervID));
            command.CommandText = "SELECT [id], [title], [author_id], [state] FROM [Interviews] WHERE [id] = @id";

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    interv.Title = (string)reader["title"];
                    interv.ID = (int)reader["id"];
                    interv.AuthorID = (int)reader["author_id"];
                    switch ((int)reader["state"])
                    {
                        case 0: interv.State = InterviewState.Draft; break;
                        case 1: interv.State = InterviewState.Actual; break;
                        case 2: interv.State = InterviewState.Closed; break;
                        default: interv.State = InterviewState.Closed; break;
                    }
                }
                else
                {
                    return interv;
                }
            }

            command.CommandText = "SELECT [id], [text], [answers], [type] FROM [Questions] WHERE [intev_id] = @id";  // Fail with writing interv_id
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Question quest = new Question();
                        quest.ID = (int)reader["id"];
                        quest.QuestionText = (string)reader["text"];
                        switch ((int)reader["type"])
                        {
                            case 0: quest.Type = QuestionType.MultipleChoice; break;
                            case 1: quest.Type = QuestionType.SingleChoice; break;
                            case 2: quest.Type = QuestionType.TextInput; break;
                            default: quest.Type = QuestionType.TextInput; break;
                        }
                        if (quest.Type != QuestionType.TextInput)
                        {
                            quest.Answers = ((string)reader["answers"]).Split(new string[] { "$$$" }, StringSplitOptions.RemoveEmptyEntries).ToList();
                        }
                        interv.AddQuestion(quest);
                    }
                }
                return interv;
            }
        }

        public int NewInterviewRet(Interview src)
        {
            this.NewInterview(src);
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = "SELECT @@IDENTITY AS [id]";

            var srcid = -1;
            var res = command.ExecuteScalar();
            if (res != null)
            {
                int.TryParse(res.ToString(), out srcid);
            }
            return srcid;
        }

        public void NewInterview(Interview src)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;

            command.Parameters.Add(new SqlParameter("@title", src.Title));
            command.Parameters.Add(new SqlParameter("@author", src.AuthorID));
            command.Parameters.Add(new SqlParameter("@state", (int)src.State));
            command.CommandText = "INSERT INTO [Interviews] ([title], [author_id], [state]) VALUES(@title,@author,@state)";
            command.ExecuteNonQuery();
            command.CommandText = "SELECT @@IDENTITY AS [id]";

            command.Parameters.Clear();

            var srcid = -1;
            var res = command.ExecuteScalar();
            if (res != null)
            {
                int.TryParse(res.ToString(), out srcid);
            }
            else
            {
                return;
            }

            command.Parameters.Clear();
            command.Parameters.Add(new SqlParameter("@interv_id", srcid));
            command.Parameters.Add(new SqlParameter("@question_text", SqlDbType.VarChar));
            command.Parameters.Add(new SqlParameter("@answers", SqlDbType.VarChar));
            command.Parameters.Add(new SqlParameter("@type", SqlDbType.Int));
            command.CommandText = "INSERT INTO [Questions] ([intev_id], [text], [answers], [type]) VALUES (@interv_id, @question_text, @answers, @type)";
            foreach (var quest in src.Questions)
            {
                command.Parameters["@question_text"].Value = quest.QuestionText;
                command.Parameters["@answers"].Value = string.Join("$$$", quest.Answers);
                command.Parameters["@type"].Value = (int)quest.Type;
                command.ExecuteNonQuery();
            }
        }

        public void ModifyInterview(int intervID, Interview src)
        {
            if (!this.InterviewExist(intervID))
            {
                return;
            }

            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;

            command.Parameters.Add(new SqlParameter("@title", src.Title));
            command.Parameters.Add(new SqlParameter("@author", src.AuthorID));
            command.Parameters.Add(new SqlParameter("@state", (int)src.State));
            command.Parameters.Add(new SqlParameter("@interv_id", intervID));

            command.CommandText = "UPDATE [Interviews] SET [title]=@title, [author_id]=@author, [state]=@state WHERE [id]=@interv_id";
            command.ExecuteNonQuery();



            command.CommandText = "SELECT [id] FROM [Questions] WHERE [intev_id] = @interv_id";

            var qlist = new List<int>();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        qlist.Add((int)reader["id"]);
                    }
                }
            }

            command.Parameters.Clear();
            command.Parameters.Add(new SqlParameter("@quest_id", SqlDbType.Int));
            foreach (int id in qlist)
            {
                command.Parameters["@quest_id"].Value = id;
                command.CommandText = "DELETE FROM [PersonalInterviewResults] WHERE [quest_id]=@quest_id";
                command.ExecuteNonQuery();
                command.CommandText = "DELETE FROM [Questions] WHERE [id]=@quest_id";
                command.ExecuteNonQuery();
            }

            command.Parameters.Clear();
            command.Parameters.Add(new SqlParameter("@interv_id", intervID));
            command.Parameters.Add(new SqlParameter("@question_text", SqlDbType.VarChar));
            command.Parameters.Add(new SqlParameter("@answers", SqlDbType.VarChar));
            command.Parameters.Add(new SqlParameter("@type", SqlDbType.Int));
            command.CommandText = "INSERT INTO [Questions] ([intev_id], [text], [answers], [type]) VALUES (@interv_id, @question_text, @answers, @type)";
            foreach (var quest in src.Questions)
            {
                command.Parameters["@question_text"].Value = quest.QuestionText;
                command.Parameters["@answers"].Value = string.Join("$$$", quest.Answers);
                command.Parameters["@type"].Value = (int)quest.Type;
                command.ExecuteNonQuery();
            }

        }

        public void SetState(int intervID, InterviewState state)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.Parameters.Add(new SqlParameter("@state", (int)state));
            command.Parameters.Add(new SqlParameter("@interv_id", intervID));
            command.CommandText = "UPDATE [Interviews] SET [state] = @state WHERE [id] = @interv_id";
            command.ExecuteNonQuery();
        }

        public InterviewState GetState(int intervID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.Parameters.Add(new SqlParameter("@interv_id", intervID));
            command.CommandText = "SELECT [state] FROM [Interviews] WHERE [id] = @interv_id";

            int state = 0;
            var res = command.ExecuteScalar();
            if (res != null)
            {
                int.TryParse(res.ToString(), out state);
            }
            return (InterviewState)state;
        }

        public void RemoveInterview(int intervID)
        {
            var interv = this.LoadInterview(intervID);
            if (interv.ID == -1)
            {
                return;
            }

            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = "DELETE FROM [Questions] WHERE [id] = @quest_id";

            command.Parameters.Add(new SqlParameter("@quest_id", SqlDbType.Int));
            foreach (var quest in interv.Questions)
            {
                int id = quest.ID;
                command.Parameters["@quest_id"].Value = id;
                command.CommandText = "DELETE FROM [PersonalInterviewResults] WHERE [quest_id]=@quest_id";
                command.ExecuteNonQuery();
                command.CommandText = "DELETE FROM [Questions] WHERE [id]=@quest_id";
                command.ExecuteNonQuery();
            }
            command.Parameters.Clear();
            command.Parameters.Add(new SqlParameter("@interv_id", intervID));
            command.CommandText = "DELETE FROM [Interviews] WHERE [id] = @interv_id";
            command.ExecuteNonQuery();


        }

        public InterviewState GetInterviewState(int intervID)
        {
            return this.LoadInterview(intervID).State;
        }

        public bool InterviewExist(int intervID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.Parameters.Add(new SqlParameter("@interv_id", intervID));
            command.CommandText = "SELECT [id] FROM [Interviews] WHERE [id] = @interv_id";
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    return true;
                }
                return false;
            }
        }

        public bool InterviewExist(string title)
        {
            return GetInterviewID(title) != -1;
        }

        public int GetInterviewID(string title)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.Parameters.Add(new SqlParameter("@title", title));
            command.CommandText = "SELECT [id] FROM [Interviews] WHERE [title] = @title";

            int id = -1;
            var res = command.ExecuteScalar();
            if (res != null)
            {
                int.TryParse(res.ToString(), out id);
            }
            return id;

        }

        public void LockInterview(int intervID)
        {
            return; // Maybe will be implemented in future
        }

        public void UnlockInterview(int intervID)
        {
            return;
        }

        public InterviewResultData GetResultDataForInterview(int intervID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.Parameters.Add(new SqlParameter("@interv_id", intervID));
            command.CommandText = "SELECT [quest_id], [user_id], [answer] FROM [PersonalInterviewResults] WHERE [interv_id] = @interv_id";

            using (SqlDataReader reader = command.ExecuteReader())
            {

                InterviewResultData ird = new InterviewResultData(intervID);
                if (reader.HasRows)
                {
                    ird.SourceInterviewID = intervID;
                    while (reader.Read())
                    {
                        int qid=(int)reader["quest_id"];
                        string answ=(string)reader["answer"];
                        if (ird.ResultData.ContainsKey(qid))
                        {
                            if (ird.ResultData[qid].ContainsKey(answ))
                            {
                                ird.ResultData[qid][answ]++;
                            }
                            else
                            {
                                ird.ResultData[qid].Add(answ, 1);
                            }
                        }
                        else
                        {
                            var newdict = new Dictionary<string, int>();
                            newdict.Add(answ, 1);
                            ird.ResultData.Add(qid, newdict);
                        }
                    }
                    return ird;
                }
                return new InterviewResultData(intervID);
            }

        }

        public void SaveInterviewRunResultData(InterviewRunResultData res)
        {
            SaveInterviewRunResultData(res.InterviewID, res);
        }

        public void SaveInterviewRunResultData(int interviewID, InterviewRunResultData interv_result)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;

            command.Parameters.Add(new SqlParameter("@interv_id", interviewID));
            command.Parameters.Add(new SqlParameter("@user_id", interv_result.UserID));
            command.Parameters.Add(new SqlParameter("@quest_id", SqlDbType.Int));
            command.Parameters.Add(new SqlParameter("@answer", SqlDbType.VarChar));
            command.Parameters.Add(new SqlParameter("@quest_type", SqlDbType.Int));

            command.CommandText = "INSERT INTO [PersonalInterviewResults] ([user_id], [interv_id], [quest_id], [answer], [quest_type]) VALUES(@user_id,@interv_id,@quest_id,@answer,@quest_type)";


            foreach (var qres in interv_result.QuestionResults)
            {
                command.Parameters["@quest_id"].Value = qres.QuestionID;
                command.Parameters["@quest_type"].Value = (int)qres.Type;
                switch (qres.Type)
                {
                    case QuestionType.MultipleChoice:
                        command.Parameters["@answer"].Value = string.Join(",", qres.MultiplePicked);
                        break;
                    case QuestionType.SingleChoice:
                        command.Parameters["@answer"].Value = qres.SinglePicked.ToString();
                        break;
                    case QuestionType.TextInput:
                        command.Parameters["@answer"].Value = qres.TextAnswer;
                        break;
                    default: continue;
                }
                command.ExecuteNonQuery();
            }
        }

        public List<Interview> GetAllInterviews(string condition)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [id] FROM [Interviews] WHERE {0}", condition);

            var list = new List<Interview>();
            var ids = new List<int>();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        ids.Add((int)reader["id"]);
                    }
                }
                else
                {
                    return new List<Interview>();
                }
            }
            foreach (var id in ids)
            {
                list.Add(this.LoadInterview(id));
            }
            return list;
        }

        public InterviewRunResultData GetDetailedStatisticsForInterview(int intervID, int userID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;

            command.Parameters.Add(new SqlParameter("@interv_id", intervID));
            command.Parameters.Add(new SqlParameter("@user_id", userID));

            command.CommandText = "SELECT [quest_id], [user_id], [answer], [quest_type] FROM [PersonalInterviewResults] WHERE [interv_id] = @interv_id AND [user_id] = @user_id";
            using (SqlDataReader reader = command.ExecuteReader())
            {

                if (reader.HasRows)
                {
                    var lst = new List<QuestionRunResultData>();
                    while (reader.Read())
                    {
                        QuestionRunResultData qrd;
                        switch ((int)reader["quest_type"])
                        {
                            case 0:
                                qrd = new QuestionRunResultData((int)reader["quest_id"], ((string)reader["answer"]).Split(',').Select((x) => (int.Parse(x))).ToList());
                                break;
                            case 1:
                                qrd = new QuestionRunResultData((int)reader["quest_id"], int.Parse((string)reader["answer"]));
                                break;
                            case 2:
                                qrd = new QuestionRunResultData((int)reader["quest_id"], (string)reader["answer"]);
                                break;
                            default:
                                qrd = new QuestionRunResultData((int)reader["quest_id"], (string)reader["answer"]);
                                break;
                        }
                        lst.Add(qrd);
                    }
                    return new InterviewRunResultData(intervID, userID, lst);
                }
                return null;
            }

        }
    }
}
